package qc.module.qms.service;

import org.springframework.stereotype.Service;
import qc.common.core.utils.DateUtil;
import qc.common.core.utils.SqlRunnerUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * ExchangeDataService
 *
 * @author QuCheng Tech
 * @since 2024/1/23
 */
@Service
public class ExchangeDataService {

    public boolean testRunner() throws SQLException {
        //Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/cms?useUnicode=true&characterEncoding=UTF-8&useSSL=false&rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai", "root", "root");
        Connection connection = DriverManager.getConnection("jdbc:sqlserver://122.9.137.64:10087;DatabaseName=Telemeter;encrypt=false", "sa", "valleybird-3114");
        org.apache.ibatis.jdbc.SqlRunner runner = new org.apache.ibatis.jdbc.SqlRunner(connection);
        System.out.println("org.apache.ibatis.jdbc.SqlRunner " + runner.toString());
        List<Map<String, Object>> result = runner.selectAll("select inserttime as tm,timingvalue1 as val from DeviceTimingDataRecord where inserttime>='2024-01-31 18:08:59' order by inserttime asc");
        for (Map<String, Object> row : result) {
            for (String key : row.keySet()) {
                System.out.println(key + " -> " + row.get(key));
            }
        }
        //org.apache.ibatis.jdbc.SqlRunner
        //com.baomidou.mybatisplus.extension.toolkit.SqlRunner.db()

        return false;
    }

    public boolean update(String url, String user, String password, String sql, String[] stcds, Date beginTime, Date endTime) throws SQLException {
        //方法调用传参为动态参数，可以根据输入的参数数量动态构造（前端传入已经固定参数为站点编码、起始时间、截止时间)
        if (stcds != null && stcds.length > 0x0) {
            //如果有传入站点编码，需要对站点编码进行拼接
            String conditionStcdValue = stcds[0x0];
            if (stcds.length == 0x1) {
                //只有1个站点编码，直接赋值，不用拼接
                conditionStcdValue = stcds[0x0];
            } else {
                //有多个站点编码，进行拼接
                StringBuilder sb = new StringBuilder();
                for (String stcd : stcds) {
                    sb.append(stcd + ",");
                }
                //移除最后的逗号
                sb.deleteCharAt(sb.length() - 1);
                conditionStcdValue = sb.toString();
            }
            //判断输入的2个时间参数，如果为空表示没有参数
            if (beginTime == null) {
                if (endTime == null) {
                    return SqlRunnerUtil.update(url, user, password, sql, conditionStcdValue);
                } else {
                    return SqlRunnerUtil.update(url, user, password, sql, conditionStcdValue, DateUtil.getDateTimeString(endTime));
                }
            } else {
                if (endTime == null) {
                    return SqlRunnerUtil.update(url, user, password, sql, conditionStcdValue, DateUtil.getDateTimeString(beginTime));
                } else {
                    return SqlRunnerUtil.update(url, user, password, sql, conditionStcdValue, DateUtil.getDateTimeString(beginTime), DateUtil.getDateTimeString(endTime));
                }
            }
        } else {
            //没有站点编码的情况
            //判断输入的2个时间参数，如果为空表示没有参数
            if (beginTime == null) {
                if (endTime == null) {
                    return SqlRunnerUtil.update(url, user, password, sql);
                } else {
                    return SqlRunnerUtil.update(url, user, password, sql, DateUtil.getDateTimeString(endTime));
                }
            } else {
                if (endTime == null) {
                    return SqlRunnerUtil.update(url, user, password, sql, DateUtil.getDateTimeString(beginTime));
                } else {
                    return SqlRunnerUtil.update(url, user, password, sql, DateUtil.getDateTimeString(beginTime), DateUtil.getDateTimeString(endTime));
                }
            }
        }
    }

    public boolean delete(String url, String user, String password, String sql, String[] stcds, Date beginTime, Date endTime) throws SQLException {
        //方法调用传参为动态参数，可以根据输入的参数数量动态构造（前端传入已经固定参数为站点编码、起始时间、截止时间)
        if (stcds != null && stcds.length > 0x0) {
            //如果有传入站点编码，需要对站点编码进行拼接
            String conditionStcdValue = stcds[0x0];
            if (stcds.length == 0x1) {
                //只有1个站点编码，直接赋值，不用拼接
                conditionStcdValue = stcds[0x0];
            } else {
                //有多个站点编码，进行拼接
                StringBuilder sb = new StringBuilder();
                for (String stcd : stcds) {
                    sb.append(stcd + ",");
                }
                //移除最后的逗号
                sb.deleteCharAt(sb.length() - 1);
                conditionStcdValue = sb.toString();
            }
            //判断输入的2个时间参数，如果为空表示没有参数
            if (beginTime == null) {
                if (endTime == null) {
                    return SqlRunnerUtil.delete(url, user, password, sql, conditionStcdValue);
                } else {
                    return SqlRunnerUtil.delete(url, user, password, sql, conditionStcdValue, DateUtil.getDateTimeString(endTime));
                }
            } else {
                if (endTime == null) {
                    return SqlRunnerUtil.delete(url, user, password, sql, conditionStcdValue, DateUtil.getDateTimeString(beginTime));
                } else {
                    return SqlRunnerUtil.delete(url, user, password, sql, conditionStcdValue, DateUtil.getDateTimeString(beginTime), DateUtil.getDateTimeString(endTime));
                }
            }
        } else {
            //没有站点编码的情况
            //判断输入的2个时间参数，如果为空表示没有参数
            if (beginTime == null) {
                if (endTime == null) {
                    return SqlRunnerUtil.delete(url, user, password, sql);
                } else {
                    return SqlRunnerUtil.delete(url, user, password, sql, DateUtil.getDateTimeString(endTime));
                }
            } else {
                if (endTime == null) {
                    return SqlRunnerUtil.delete(url, user, password, sql, DateUtil.getDateTimeString(beginTime));
                } else {
                    return SqlRunnerUtil.delete(url, user, password, sql, DateUtil.getDateTimeString(beginTime), DateUtil.getDateTimeString(endTime));
                }
            }
        }
    }

    /***
     * 执行查询sql语句
     *
     * @param sql 查询sql语句
     * @param stcds 站点编码集合参数，可以为空、1个或多个
     * @param beginTime 数据起始时间
     * @param endTime 数据截止时间
     * @return java.util.List<java.util.Map < java.lang.String, java.lang.Object>>
     * @author QuCheng Tech
     * @since 2024/1/31
     */
    public List<Map<String, Object>> query(String url, String user, String password, String sql, String[] stcds, Date beginTime, Date endTime) throws SQLException {
        //方法调用传参为动态参数，可以根据输入的参数数量动态构造（前端传入已经固定参数为站点编码、起始时间、截止时间)
        if (stcds != null && stcds.length > 0x0) {
            //如果有传入站点编码，需要对站点编码进行拼接
            String conditionStcdValue = stcds[0x0];
            if (stcds.length == 0x1) {
                //只有1个站点编码，直接赋值，不用拼接
                conditionStcdValue = stcds[0x0];
            } else {
                //有多个站点编码，进行拼接
                StringBuilder sb = new StringBuilder();
                for (String stcd : stcds) {
                    sb.append(stcd + ",");
                }
                //移除最后的逗号
                sb.deleteCharAt(sb.length() - 1);
                conditionStcdValue = sb.toString();
            }
            //判断输入的2个时间参数，如果为空表示没有参数
            if (beginTime == null) {
                if (endTime == null) {
                    return SqlRunnerUtil.selectAll(url, user, password, sql, conditionStcdValue);
                } else {
                    return SqlRunnerUtil.selectAll(url, user, password, sql, conditionStcdValue, DateUtil.getDateTimeString(endTime));
                }
            } else {
                if (endTime == null) {
                    return SqlRunnerUtil.selectAll(url, user, password, sql, conditionStcdValue, DateUtil.getDateTimeString(beginTime));
                } else {
                    return SqlRunnerUtil.selectAll(url, user, password, sql, conditionStcdValue, DateUtil.getDateTimeString(beginTime), DateUtil.getDateTimeString(endTime));
                }
            }
        } else {
            //没有站点编码的情况
            //判断输入的2个时间参数，如果为空表示没有参数
            if (beginTime == null) {
                if (endTime == null) {
                    return SqlRunnerUtil.selectAll(url, user, password, sql);
                } else {
                    return SqlRunnerUtil.selectAll(url, user, password, sql, DateUtil.getDateTimeString(endTime));
                }
            } else {
                if (endTime == null) {
                    return SqlRunnerUtil.selectAll(url, user, password, sql, DateUtil.getDateTimeString(beginTime));
                } else {
                    return SqlRunnerUtil.selectAll(url, user, password, sql, DateUtil.getDateTimeString(beginTime), DateUtil.getDateTimeString(endTime));
                }
            }
        }
    }

    /***
     * 执行查询sql语句
     *
     * @param sql 查询sql语句
     * @param stcds 站点编码集合参数，可以为空、1个或多个
     * @param beginTime 数据起始时间
     * @param endTime 数据截止时间
     * @return java.util.List<java.util.Map < java.lang.String, java.lang.Object>>
     * @author QuCheng Tech
     * @since 2024/1/31
     */
    public Map<String, Object> queryOne(String url, String user, String password, String sql, String[] stcds, Date beginTime, Date endTime) throws SQLException {
        //方法调用传参为动态参数，可以根据输入的参数数量动态构造（前端传入已经固定参数为站点编码、起始时间、截止时间)
        if (stcds != null && stcds.length > 0x0) {
            //如果有传入站点编码，需要对站点编码进行拼接
            String conditionStcdValue = stcds[0x0];
            if (stcds.length == 0x1) {
                //只有1个站点编码，直接赋值，不用拼接
                conditionStcdValue = stcds[0x0];
            } else {
                //有多个站点编码，进行拼接
                StringBuilder sb = new StringBuilder();
                for (String stcd : stcds) {
                    sb.append(stcd + ",");
                }
                //移除最后的逗号
                sb.deleteCharAt(sb.length() - 1);
                conditionStcdValue = sb.toString();
            }
            //判断输入的2个时间参数，如果为空表示没有参数
            if (beginTime == null) {
                if (endTime == null) {
                    return SqlRunnerUtil.selectOne(url, user, password, sql, conditionStcdValue);
                } else {
                    return SqlRunnerUtil.selectOne(url, user, password, sql, conditionStcdValue, DateUtil.getDateTimeString(endTime));
                }
            } else {
                if (endTime == null) {
                    return SqlRunnerUtil.selectOne(url, user, password, sql, conditionStcdValue, DateUtil.getDateTimeString(beginTime));
                } else {
                    return SqlRunnerUtil.selectOne(url, user, password, sql, conditionStcdValue, DateUtil.getDateTimeString(beginTime), DateUtil.getDateTimeString(endTime));
                }
            }
        } else {
            //没有站点编码的情况
            //判断输入的2个时间参数，如果为空表示没有参数
            if (beginTime == null) {
                if (endTime == null) {
                    return SqlRunnerUtil.selectOne(url, user, password, sql);
                } else {
                    return SqlRunnerUtil.selectOne(url, user, password, sql, DateUtil.getDateTimeString(endTime));
                }
            } else {
                if (endTime == null) {
                    return SqlRunnerUtil.selectOne(url, user, password, sql, DateUtil.getDateTimeString(beginTime));
                } else {
                    return SqlRunnerUtil.selectOne(url, user, password, sql, DateUtil.getDateTimeString(beginTime), DateUtil.getDateTimeString(endTime));
                }
            }
        }
    }

    /***
     * 获取最新数据的数据时标，如果不存在返回null
     *
     * @param tableCode 数据库表名
     * @param tmColumnName 数据时标列名
     * @param stcdColumnName 站点编码列名
     * @param stcd 站点编码
     * @param noMoreThanTime 不大于的时间点
     * @return java.util.Date
     * @author QuCheng Tech
     * @since 2024/1/31
     */
    public Date getLatestDataTime(String url, String user, String password, String tableCode, String tmColumnName, String stcdColumnName, String stcd, Date noMoreThanTime) {
        try {
            //根据指定表、最大时间查询最新数据时标
            //固定为：select tm from tableCode where stcd='' and tm<now() order by tm desc limit 1;
            //mybatis中sql语句的占位符不能使用{0}，要使用问号?
            //String sql = "select " + tmColumnName + " from " + tableCode + " where " + stcdColumnName + "={0} and " + tmColumnName + "<{1} order by " + tmColumnName + " desc limit 1";
            String sql = "select " + tmColumnName + " from " + tableCode + " where " + stcdColumnName + "=? and " + tmColumnName + "<? order by " + tmColumnName + " desc limit 1";
            //String sql = "select " + tmColumnName + " from " + tableCode + " where " + stcdColumnName + "='" + stcd + "' and " + tmColumnName + "<'" + DateUtil.getDateTimeString(noMoreThanTime) + "' order by " + tmColumnName + " desc limit 1";
            System.out.println("ExchangeDataService.getLatestDataTime sql=" + sql);
            System.out.println("ExchangeDataService.getLatestDataTime stcd=" + stcd);
            System.out.println("ExchangeDataService.getLatestDataTime noMoreThanTime=" + noMoreThanTime);
            Map<String, Object> selectResult = SqlRunnerUtil.selectOne(url, user, password, sql, stcd, DateUtil.getDateTimeString(noMoreThanTime));
            //Map<String, Object> selectResult = SqlRunnerUtil.selectOne(url, user, password, sql);
            if (selectResult != null) {
                for (String key : selectResult.keySet()) {
                    Object objValue = selectResult.get(key);
                    Date resultDate = DateUtil.parseDate(objValue);
                    System.out.println("getLatestDataTime obj=" + objValue.toString() + " parseDate=" + resultDate.toString());
                    return resultDate;
                }
            }
        } catch (SQLException ex) {
            //使用SqlRunnerUtil.selectOne方法查询不到数据时会抛出异常
        }

        return null;
    }

    /***
     * 获取交换数据表中的时间和数据值，指定站点、指定时间段、指定1个数据列
     *
     * @param tableCode 数据库表名
     * @param tmColumnName 数据时标列名
     * @param stcdColumnName 站点编码列名
     * @param valueColumnName 数据值列名
     * @param stcd 站点编码
     * @param beginTime 起始时间
     * @param endTime 截止时间
     * @return java.util.LinkedHashMap<java.util.Date, java.lang.Double>
     * @author QuCheng Tech
     * @since 2024/1/31
     */
    public LinkedHashMap<Date, Double> getData(String url, String user, String password, String tableCode, String tmColumnName, String stcdColumnName, String valueColumnName, String stcd, Date beginTime, Date endTime) throws SQLException {
        //根据指定的表和列查询指定站点编码一段时间范围内的数据，最终返回的是时间和值的序列
        //固定为：select inserttime,timingvalue1 from table where stcd='' and tm>='' and tm<='' order by tm asc
        //String sql = "select " + tmColumnName + " as tm," + valueColumnName + " as val from " + tableCode + " where " + stcdColumnName + "={2} and " + tmColumnName + ">={0} and " + tmColumnName + "<={1} order by " + tmColumnName + " asc";
        String sql = "select " + tmColumnName + " as tm," + valueColumnName + " as val from " + tableCode + " where " + stcdColumnName + "=? and " + tmColumnName + ">=? and " + tmColumnName + "<=? order by " + tmColumnName + " asc";
        //String sql = "select " + tmColumnName + " as tm," + valueColumnName + " as val from " + tableCode + " where " + stcdColumnName + "='" + stcd + "' and " + tmColumnName + ">='" + DateUtil.getDateTimeString(beginTime) + "' and " + tmColumnName + "<='" + DateUtil.getDateTimeString(endTime) + "' order by " + tmColumnName + " asc";
        List<Map<String, Object>> queryResult = this.query(url, user, password, sql, new String[]{stcd}, beginTime, endTime);
        //System.out.println("ExchangeDataService getData queryResult count=" + queryResult.size());
        //List<Map<String, Object>> queryResult = SqlRunnerUtil.selectAll(url, user, password, sql);
        if (queryResult != null && queryResult.size() > 0x0) {
            LinkedHashMap<Date, Double> result = new LinkedHashMap<>();
            //HashMap<Date, Double> result = new HashMap<>();

            for (Map<String, Object> row : queryResult) {
                //sql中已经固定使用tm，value，直接取值
                //实测得到的tmObject和valueObject均为null；实测得到的列名为大写，使用小写无法获取；
                //System.out.println("ExchangeDataService getData queryResult row tmObject=" + tmObject + " valueObject=" + valueObject);
                //for (String key : row.keySet()) {
                //    System.out.println("ExchangeDataService getData queryResult row key=" + key + " value=" + row.get(key));
                //}
                //Object tmObject = row.get("TM");
                //Object valueObject = row.get("VAL");
                //后修改已在SqlRunnerUtil.selectAll方法中将返回结果的所有列名均处理为小写字母，在此使用小写字母列名获取对应的数据
                Object tmObject = row.get("tm");
                Object valueObject = row.get("val");

                if (tmObject != null && valueObject != null) {
                    Date tm = DateUtil.parseDate(tmObject);
                    System.out.println("ExchangeDataService getData queryResult row tmObject=" + tmObject + " valueObject=" + valueObject + " tm=" + tm);
                    if (tm != null) {
                        double value = Double.parseDouble(valueObject.toString());

                        result.put(tm, value);
                    }
                }
            }
            System.out.println("ExchangeDataService getData result count=" + result.size());
            return result;
        }

        return null;
    }

    /***
     * 获取交换数据表中最新1条数据的时间和数据值，指定站点、指定不大于的时间点、指定1个数据列
     *
     * @param tableCode 数据库表名
     * @param tmColumnName 数据时标列名
     * @param stcdColumnName 站点编码列名
     * @param valueColumnName 数据值列名
     * @param stcd 站点编码
     * @param noMoreThanTime 起时间
     * @return java.util.LinkedHashMap<java.util.Date, java.lang.Double>
     * @author QuCheng Tech
     * @since 2024/1/31
     */
    public LinkedHashMap<Date, Double> getLatestData(String url, String user, String password, String tableCode, String tmColumnName, String stcdColumnName, String valueColumnName, String stcd, Date noMoreThanTime) throws SQLException {
        //根据指定的表和列查询指定站点编码一段时间范围内的数据，最终返回的是时间和值的序列
        //固定为：select inserttime,timingvalue1 from table where stcd='' and tm<='' order by tm desc limit 1
        //String sql = "select " + tmColumnName + " as tm," + valueColumnName + " as val from " + tableCode + " where " + stcdColumnName + "={0} and " + tmColumnName + "<={1} order by " + tmColumnName + " desc limit 1";
        //String sql = "select " + tmColumnName + " as tm," + valueColumnName + " as val from " + tableCode + " where " + stcdColumnName + "=? and " + tmColumnName + "<=? order by " + tmColumnName + " desc limit 1";
        //sql server没有limit语法，使用top替代
        String sql = "select top 1 " + tmColumnName + " as tm," + valueColumnName + " as val from " + tableCode + " where " + stcdColumnName + "=? and " + tmColumnName + "<=? order by " + tmColumnName + " desc";
        List<Map<String, Object>> queryResult = this.query(url, user, password, sql, new String[]{stcd}, noMoreThanTime, null);
        if (queryResult != null && queryResult.size() > 0x0) {
            LinkedHashMap<Date, Double> result = new LinkedHashMap<>();
            //HashMap<Date, Double> result = new HashMap<>();

            for (Map<String, Object> row : queryResult) {
                //sql中已经固定使用tm，value，直接取值
                Object tmObject = row.get("tm");
                Object valueObject = row.get("val");
                //后修改已在SqlRunnerUtil.selectAll方法中将返回结果的所有列名均处理为小写字母，在此使用小写字母列名获取对应的数据
                if (tmObject != null && valueObject != null) {
                    Date tm = DateUtil.parseDate(tmObject);
                    if (tm != null) {
                        double value = Double.parseDouble(valueObject.toString());

                        result.put(tm, value);
                    }
                }
            }
            System.out.println("ExchangeDataService getLatestData result count=" + result.size());
            return result;
        }

        return null;
    }
}
